# -*- coding: utf-8 -*-
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
#作者：cacho_37967865
#博客：https://blog.csdn.net/sinat_37967865
#文件：excelTxt.py
#日期：2019-08-13
#备注：Excel格式内容与Txt格式内容相互转换  
2003以前的表格以.xls后缀，用xlwt来写表格，用xlrd来读取表格 第一行第一列坐标为0,0
2007的表格以.xlsx后缀，用openpyxl来读写表格。 第一行第一列坐标为1,1
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

import xlrd
import xlwt
from openpyxl import load_workbook

file_path = 'E:\zenglingwei\\test\\'


def xls_to_txt(file,file_type):
    excel = xlrd.open_workbook(file)             # 打开excle文档读取数据
    table = excel.sheets()[0]
    nrows = table.nrows
    print('xls格式sheet页总行数为：',nrows)
    for i in range(1, nrows):  # 第2行开始
        name = table.cell_value(i, 0)     # 第一列开始
        phone = table.cell_value(i, 1)
        idCard = table.cell_value(i, 2)
        inCome = table.cell_value(i, 3)
        usrJob = table.cell_value(i, 4)
        schooling = table.cell_value(i, 5)
        maritalSta = table.cell_value(i, 6)
        cusSex = table.cell_value(i, 7)
        companyAddressCode = table.cell_value(i, 8)
        addressCode = table.cell_value(i, 9)
        print(name,phone,idCard,inCome,usrJob,schooling,maritalSta,cusSex,companyAddressCode,addressCode)
        save_txt(name + '|' + str(phone) + '|' + str(idCard) + '|' + inCome+ '|' +usrJob+ '|' +schooling+ '|' +str(maritalSta)+ '|' +str(cusSex)+ '|' +companyAddressCode+ '|' +addressCode,file,file_type)


def xlsx_to_txt(file,file_type):
    excel = load_workbook(file)         # 打开excle文档读取数据
    table = excel['Sheet1']
    nrows = table.max_row
    print('xlsx格式sheet页总行数为：',nrows)
    for i in range(2, nrows + 1):  # 第2行开始
        name = table.cell(row=i,column=1).value     # 第一列开始
        phone = table.cell(row=i,column=2).value
        idCard = table.cell(row=i,column=3).value
        inCome = table.cell(row=i,column=4).value
        usrJob = table.cell(row=i,column=5).value
        schooling = table.cell(row=i,column=6).value
        maritalSta = table.cell(row=i,column=7).value
        cusSex = table.cell(row=i,column=8).value
        companyAddressCode = table.cell(row=i,column=9).value
        addressCode = table.cell(row=i,column=10).value
        print(name,phone,idCard,inCome,usrJob,schooling,maritalSta,cusSex,companyAddressCode,addressCode)
        save_txt(name + '|' + str(phone) + '|' + str(idCard) + '|' + inCome+ '|' +usrJob+ '|' +schooling+ '|' +str(maritalSta)+ '|' +str(cusSex)+ '|' +companyAddressCode+ '|' +addressCode,file,file_type)



def txt_to_excel(file):
    data = []
    with open(file, 'r',encoding= 'utf-8') as fp:
        for line in fp.readlines():
            line = line.lstrip().replace(' ','').replace('\n','')
            data.append(line)
    return(data)


def save_txt(word,file,file_type):
    files = file.replace(file_type,'txt')
    with open(files, 'a',encoding= 'utf-8') as fp:
        fp.write(word+'\n')


def save_xls(file,file_type):
    info = txt_to_excel(file)
    wb = xlwt.Workbook()                       # 打开一个空白excel
    sheet = wb.add_sheet("测试")                # 添加表格名称
    for i in range(0,len(info)):
        name = info[i].split('|')[0]
        phone = info[i].split('|')[1]
        idCard = info[i].split('|')[2]
        print(name,phone,idCard)
        sheet.write(i +1 ,0, name)                 # 第二行开始第一列
        sheet.write(i + 1, 1, phone)
        sheet.write(i + 1, 2, idCard)

    wb.save(file.replace(file_type,'xls'))
    print("写入表格成功！！！")


def main(file_name):
    file = file_path + file_name
    file_type = file.split('.')[1]
    if file_type == 'xlsx':
        xlsx_to_txt(file,file_type)
    elif file_type == 'xls':
        xls_to_txt(file, file_type)
    elif file_type == 'txt':
        save_xls(file,file_type)


if __name__ == '__main__':
    main('test20190924.xlsx')

